package com.mbbmap.app.home;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbbmap.app.*;
import com.mbbmap.app.dao.*;
import com.mbbmap.app.exception.*;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.util.Constants;

/**
 * @author Lue
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class PropertyHome {
    
	private static PropertyHome instance = null;
	
	/**
	 * Returns the single instance, creating one if it's the
	 * first time this method is called.
	 *
	 * @return PropertyHome. The single instance.
	 */
	public static PropertyHome getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (PropertyHome.class) {
				if (instance == null) {
					instance = new PropertyHome();
				}
			}
		}
		return instance;
	}
	
	private PropertyHome() {
		super();
	}
	
	
	/**
	 * Get Property Definition ID base on given propName and object
	 * @param propName
	 * @param obj
	 * @return
	 */
    public long getPropertyDefId(String propName, Object obj) {
        
        String querySql = null;
        long propId = -1;
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dba = DBAccessHome.getInstance();
        String fullClassName = obj.getClass().getName();
        
        try {
            connection = dba.getDBConnection(); 
            
            if (connection == null) {
                System.err.println("PropertyHome.getPropertyDefId(): DB Connection is null");
                throw new EbppException ("DB Connection is null");
            }
            querySql = "select prop_id from  " + Constants.DB_TBL_PROPERTYDEFINITION + " where owner=?  and prop_name=?";
            pstmtQuery =  connection.prepareStatement(querySql);
            pstmtQuery.setString(1, fullClassName.substring(fullClassName.lastIndexOf('.')+1));
            pstmtQuery.setString(2, propName);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                propId = rs.getInt(1);
            } 
            rs.close();
            
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("PropertyHome.getPropertyDefId(): Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error in PropertyHome.getPropertyDefId() : \n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error in PropertyHome.getPropertyDefId() : \n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error in PropertyHome.getPropertyDefId() : Error closing pstmtQuery\n" + e1);
		            e1.printStackTrace();
				}
			}
			if (connection !=null)
			    dba.freeDBConnection (connection);
		}
        
        return propId;
    }
    
    /**
     * set property definition for the given class and property name
     * @param propName property name
     * @param obj object to get the class name
     * @param desc description for the property defined
     * @return
     */
    private boolean setPropertyDef(String propName, String className, String desc)  {

        boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtInsert = null;
        int noOfRecords=0;
        
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        
        // insert property definition record into property definition table
        String insertSql = "INSERT INTO " + Constants.DB_TBL_PROPERTYDEFINITION + " (OWNER, PROP_NAME, DESCRIPTION) " +
        " VALUES (?, ?, ?)";

        try {
            
            connection = dbh.getDBConnection();
            
            //null value validation
            if (propName == null || className == null ) {
                success = false;
                throw new EbppException ("property name or class name is null");
            }
            
            if (desc == null) {
                desc= "";
            }
            
            if (connection == null) {
                System.err.println("PropertyHome.setPropertyDef(): DB Connection is null");
                success = false;
                throw new EbppException ("DB Connection is null");
            }
	            
	        pstmtInsert = connection.prepareStatement(insertSql);
	        pstmtInsert.setString(1, className);
	        pstmtInsert.setString(2, propName);
	        pstmtInsert.setString(3, desc);
	            
	        // execute the insert statement;
	        noOfRecords = pstmtInsert.executeUpdate();
	        
	        if (success && noOfRecords < 1 )
	            success = false;

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.setPropertyDef() SQL execution : " + insertSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.setPropertyDef()\n" + ebppe.getMessage());
            ebppe.printStackTrace();            
        } finally {
			if (pstmtInsert != null) {
				try {
				    pstmtInsert.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtInsert for PropertyHome.setPropertyDef()\n" + e1);
		            e1.printStackTrace();
				}
			}
			
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}
        
        return success;
    }

    
    /**
     * set property key-value pair for the given class and instance key
     * @param key
     * @param propValue
     * @param propName
     * @param obj
     * @return
     */
    public boolean setProperty(String key, String propValue, String propName, Object obj) {

        boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtInsert = null, pstmtUpdate = null;
        int noOfRecords=0;
        long propDefId = -1;
        String insertSql = null, updateSql = null;
        String fullClassName = obj.getClass().getName();
        String className = fullClassName.substring(fullClassName.lastIndexOf('.')+1);

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        try {
            //System.out.println("=====PropertyHome.setProperty. prodName=" + propName);
            //System.out.println("=====PropertyHome.setProperty. className=" + className);
            propDefId = getPropertyDefId(propName, obj);
	        
	        // if property definition does not exist then add one
	        if (propDefId < 0) {
	            String description = "Property " + propName + " for class " + className;
	            boolean addPropDefOK = setPropertyDef(propName, className, description);
	            
	            if (!addPropDefOK) {
	                success = false;
	                throw new EbppException ("Fail to add Property Definition");
	            } else {
	                propDefId = getPropertyDefId(propName, obj);
	                
	                // get the property id after property definition was added
	                if (propDefId < 0) {
	                    throw new EbppException ("Invalid Property Definition ID");
	                }
	            }
	        }
	        
	        //ystem.out.println("=====PropertyHome.setProperty. propDefId=" + propDefId);
	        
	        // check if the property already exist
	        String existPropValue = getProperty(key, obj, propName);
	        
	        // insert property record into property table
	        insertSql = "INSERT INTO " + Constants.DB_TBL_PROPERTY + " (INSTANCE_KEY, PROP_ID, VALUE ) " +
	        " VALUES (?, ?, ?)";

	        // update property record in property table
	        updateSql = "UPDATE " + Constants.DB_TBL_PROPERTY + " SET VALUE = ? " +
	        " WHERE INSTANCE_KEY = ? AND PROP_ID = ? ";

            connection = dbh.getDBConnection();
            
            if (connection == null) {
                System.err.println("PropertyHome.setProperty(): DB Connection is null");
                throw new EbppException ("DB Connection is null");
            }
	        
            if (existPropValue == null) {
		        pstmtInsert = connection.prepareStatement(insertSql);
		        pstmtInsert.setString(1, key);
		        pstmtInsert.setLong(2, propDefId);
		        pstmtInsert.setString(3, propValue);
		            
		        // execute the insert statement;
		        noOfRecords = pstmtInsert.executeUpdate();
            } else {
		        pstmtUpdate = connection.prepareStatement(updateSql);
		        pstmtUpdate.setString(1, propValue);
		        pstmtUpdate.setString(2, key);
		        pstmtUpdate.setLong(3, propDefId);

		        // execute the update statement;
		        noOfRecords = pstmtUpdate.executeUpdate();
            }
            
	        if (success && noOfRecords < 1 )
	            success = false;
            
        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.setProperty() SQL execution : " + insertSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.setProperty()\n" + ebppe.getMessage());
            ebppe.printStackTrace();            
        } finally {
			if (pstmtInsert != null) {
				try {
				    pstmtInsert.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtInsert for PropertyHome.setProperty()\n" + e1);
		            e1.printStackTrace();
				}
			}
			
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtUpdate for PropertyHome.setProperty()\n" + e1);
		            e1.printStackTrace();
				}
			}
			
			if (connection !=null)
			   dbh.freeDBConnection (connection);
		}
        
        return success;
    }

    /**
     * Remove from the property table
     * @param key
     * @param propName
     * @param obj
     * @return
     */
    public boolean removeProperty(String key, String propName, Object obj) {

        boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtDelete = null;
        int noOfRecords=0;
        long propId = -1;
        String delSql = null;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        try {
        
	        propId = getPropertyDefId(propName, obj);
	        
	        if (propId < 0) {
	           success = false;
	        } else {
	        
		        // delete property record from property table
		        delSql = "DELETE FROM " + Constants.DB_TBL_PROPERTY + " WHERE " +
		        " INSTANCE_KEY = ? AND PROP_ID = ? ";
	            
	            connection = dbh.getDBConnection();
	            
	            if (connection == null) {
	                System.err.println("PropertyHome.setProperty(): DB Connection is null");
	                throw new EbppException ("DB Connection is null");
	            }
		            
		        pstmtDelete = connection.prepareStatement(delSql);
		        pstmtDelete.setString(1, key);
		        pstmtDelete.setLong(2, propId);
		            
		        // execute the insert statement;
		        noOfRecords = pstmtDelete.executeUpdate();
		        
		        if (success && noOfRecords < 1 )
		            success = false;
	            
	        }
	        

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.removeProperty() SQL execution : " + delSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.removeProperty()\n" + ebppe.getMessage());
            ebppe.printStackTrace();            
        } finally {
			if (pstmtDelete != null) {
				try {
				    pstmtDelete.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtDelete for PropertyHome.removeProperty()\n" + e1);
		            e1.printStackTrace();
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}
        
        return success;
    }
    
    /**
     * Remove all properties from the property table
     * @param key
     * @param propName
     * @param obj
     * @return
     */
    public boolean removeProperties (String key, Object obj) {

        boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtDelete = null;
        int noOfRecords=0;
        String delSql = null;
        String fullClassName = obj.getClass().getName();
        String className = fullClassName.substring(fullClassName.lastIndexOf('.')+1);

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        try {
        
		        // delete property record from the property table
		        delSql = "DELETE FROM "
                    + Constants.DB_TBL_PROPERTY
                    + " WHERE INSTANCE_KEY = ? AND PROP_ID IN (SELECT PROP_ID FROM "
                    + Constants.DB_TBL_PROPERTYDEFINITION
                    + " WHERE OWNER = ?) ";
	            
	            connection = dbh.getDBConnection();
	            
	            if (connection == null) {
	                System.err.println("PropertyHome.setProperty(): DB Connection is null");
	                throw new EbppException ("DB Connection is null");
	            }
		            
		        pstmtDelete = connection.prepareStatement(delSql);
		        pstmtDelete.setString(1, key);
		        pstmtDelete.setString(2, className);
		            
		        // execute the insert statement;
		        noOfRecords = pstmtDelete.executeUpdate();

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.removeProperties() SQL execution : " + delSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in PropertyHome.removeProperties()\n" + ebppe.getMessage());
            ebppe.printStackTrace();            
        } finally {
			if (pstmtDelete != null) {
				try {
				    pstmtDelete.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error closing pstmtDelete for PropertyHome.removeProperties()\n" + e1);
		            e1.printStackTrace();
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}
        
        return success;
    }
    
	/**
	 * Get Property value by instance key and property id
	 * @param instanceKey object key
	 * @param propId Property ID
	 * @return
	 */
    public String getPropertyById(String instanceKey, int propId) {
        
        String querySql = null;
        String propValue = null;
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dba = DBAccessHome.getInstance();
        
        try {
            connection = dba.getDBConnection(); 
            
            if (connection == null) {
                System.err.println("PropertyHome.getPropertyById(): DB Connection is null");
                throw new EbppException ("DB Connection is null");
            }
            querySql = "select value from  " + Constants.DB_TBL_PROPERTY + " where instance_key=?  and prop_id=?";
            pstmtQuery =  connection.prepareStatement(querySql);
            pstmtQuery.setString(1, instanceKey);
            pstmtQuery.setInt(2, propId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                propValue = rs.getString(1);
            } 
            rs.close();
            
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("PropertyHome.getPropertyById(): Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error in PropertyHome.getPropertyById() : \n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error in PropertyHome.getPropertyById() : \n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error in PropertyHome.getPropertyById() : Error closing pstmtQuery\n" + e1);
		            e1.printStackTrace();
				}
			}
			if (connection !=null)
			    dba.freeDBConnection (connection);
		}
        
        return propValue;
    }

    
	/**
	 * Get Property by object instance key, object and property name 
	 * @param propName
	 * @param obj
	 * @return
	 */
    public String getProperty(String instanceKey, Object obj, String propName) {
        
        String querySql = null;
        String propValue = null;
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dba = DBAccessHome.getInstance();
        long propId;
        
        try {
            
            propId = getPropertyDefId(propName, obj);
            
            if (propId < 0 ) {
                throw new EbppException ("Invalid property ID");
            }
            connection = dba.getDBConnection(); 
            
            if (connection == null) {
                System.err.println("PropertyHome.getProperty(): DB Connection is null");
                throw new EbppException ("DB Connection is null");
            }
            querySql = "select value from  " + Constants.DB_TBL_PROPERTY + " where instance_key=?  and prop_id=?";
            pstmtQuery =  connection.prepareStatement(querySql);
            pstmtQuery.setString(1, instanceKey);
            pstmtQuery.setLong(2, propId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                propValue = rs.getString(1);
            } 
            rs.close();
        
        } catch (EbppException ebppe) {
            sysLogMgr.logMsg("PropertyHome.getProperty(): Error in getting property : \n" + ebppe);
            ebppe.printStackTrace();    
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("PropertyHome.getProperty(): Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error in PropertyHome.getProperty() : \n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error in PropertyHome.getProperty() : \n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error in PropertyHome.getProperty() : Error closing pstmtQuery\n" + e1);
		            e1.printStackTrace();
				}
			}
			if (connection !=null)
			    dba.freeDBConnection (connection);
		}
        
        return propValue;
    }

    public static void main(String[] args) {
        PropertyHome pm = PropertyHome.getInstance();
        UserDao user = new UserDao();
        
        System.out.println("Property Id = " + pm.getPropertyDefId(UserDao.PROP_EMAIL, user));
        
    }
}
